Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

Hello, and welcome to Lesson 10. Today we'll finish off the customer checkout process by using some advanced database techniques to help us organize our checkout data. There are a lot of things that need to happen when a customer checks out, and it's your job to make sure they all happen so the customer is happy.

First, we'll take a break from coding and look at some database theory. The MySQL database server has come a long way since it first came on the scene, and it can now support some pretty advanced database operations. You'll need to take advantage of some of these new features in your checkout process.

Next, you'll discover another feature of MySQL that's crucial for the inventory aspect of the Food Store application. If you want to maintain your store inventory using the application, you'll probably want to know when you run out of an item. Unfortunately, MySQL doesn't provide this protection by default, so you'll have to do some work to implement that.

Finally, we'll turn our attention back to the application code and create our final section of code required to complete the customer checkout process.

Let's start things off by looking at some database theory!

Database Transactions

Returning to our checkout saga, we last left our customer standing at our virtual checkout counter. The customer had just registered (or logged in using an existing account) and confirmed the items in the order. We're now ready to create the final customer order and complete the checkout process.

Creating the final order requires performing three database tasks:

1. Creating a new record in the orders table to represent the new customer order.
2. Adding a record to the order_items table for each product in the customer's shopping cart, relating each ordered item to the new order number we created in the orders table.
3. Subtracting the quantity of each product ordered from the quantity data field in the products table for that product to update our inventory.

As you work through the details required to check out your customer, you'll find yourself in somewhat of a programming dilemma. All three of these database functions need to happen, or else you have a database problem on your hands. For example:

• If you create a new record in the orders table without any related records in the order_items table, you won't have a clue what items the customer purchased for the order.
• If you add items to the order_items table without subtracting the quantities from the products table, your inventory total will be off, causing problems for future orders.

Trying to tie these three functions together requires an advanced database technique called transactions.

Transactions are a crucial element to any relational database system. In a transaction, the database engine handles a group of two or more SQL statements as a single unit. If all of the SQL statements in a transaction succeed, the transaction unit succeeds and everything's fine.

However, if any one of the SQL statements in a transaction fails, you have a problem. Not only should the transaction as a whole fail, but any SQL statements in the transaction that were executed previous to the failed statement need to be undone to return the database back to how it was before you attempted the transaction.

For example, if you create the customer order record in the orders table but then the attempt to subtract the ordered products from the products table fails, you need to remove the order from the orders table and inform the customer of the issue. Having an order entered in the orders table without subtracting the products from the inventory in the products table will cause problems for your inventory.

Creating Transactions in MySQL

The MySQL database server only recently provided support for transactions (and it's only available using the InnoDB database engine). However, you need to use special SQL statements to enable transaction support.

By default, every time you enter an SQL statement in MySQL (either via the MySQL Console or using the PHP mysql_query() function), the MySQL database engine performs the statement automatically. This is called committing the statement to the database.

When an SQL statement is committed to the database, it permanently enters the results of the statement into the tables, adds inserted data to the tables, removes deleted data from the tables, and changes updated data in the tables. There's no turning back from a committed SQL statement (aside from restoring the database from a previous backup).

When MySQL automatically commits a statement to the database, it's called autocommit. MySQL enables this feature by default.

In a transaction, the MySQL server doesn't autocommit statements. You can perform individual statements, but the server doesn't apply the results permanently. You need to tell it to by using the COMMIT statement. Instead, statements are applied in a temporary state, apart from the normal database data.

To perform transactions, you must first disable the autocommit feature in MySQL. You do this using the set SQL statement:

set autocommit=0

This disables the autocommit feature for the current session and doesn't affect any other sessions connected to the server. Now no statements are permanent until you perform the COMMIT statement. At any time before the COMMIT statement, you can issue a ROLLBACK statement to undo the effect of any previously entered SQL statement.

You define a transaction as a block of SQL statements. You start the block using the START TRANSACTION statement, and you end it using either the COMMIT or ROLLBACK statement.

Testing Transactions

Let's test this feature out so you can visually see how it works. Follow these steps to test transactions in MySQL:

1. Start the MySQL Console by clicking the WampServer icon in the system tray. Then select MySQL, then MySQL Console.
2. Enter the password for your MySQL root user account (the default is no password, so just hit the ENTER key).
3. Go to the store database by entering the command:

   use store;

4. Turn off the autocommit feature by entering the command:

   set autocommit=0;

5. Start a transaction block by entering the command:

   START TRANSACTION;

6. Insert data into the categories table:

   INSERT INTO categories (name) VALUES ('test1');

7. Insert a few more test categories into the categories table using the INSERT statement.
8. List the items in the categories table to ensure your new items are there.

   SELECT * FROM categories;

9. Now roll back the transaction to prevent the process of the transaction:

   ROLLBACK;

10. List the items in the categories table again to see if your new items are still there:

    SELECT * FROM categories;

By rolling back the transaction, you undid any previous SQL statements entered since the START TRANSACTION statement. Notice that before the ROLLBACK statement, when you display the categories table contents, you see your new entries just fine, including the auto_increment values MySQL assigned to them. All of that was only a temporary copy for you to see during the transaction. None of the data was actually entered into the table. If any other user logged into the server and displayed the categories data during your transaction, they wouldn't see the data from your transaction.

Now, let's actually commit some data from a transaction. Follow these steps:

1. Start the MySQL Console again.
2. Go to the store database using the use store; statement.
3. Disable the autocommit feature using the command:

   set autocommit=0;

4. Start a new transaction using the statement:

   START TRANSACTION;

5. Enter a new category in the categories table:

   INSERT INTO categories (name) VALUES ('frozen food');

6. Commit the transaction into the table:

   COMMIT;

7. List the contents of the categories table:

   SELECT * FROM categories;

8. Exit the MySQL Console by using the exit command

Once you enter the COMMIT statement, the data you entered in your transaction appears permanently in the table. If you display the categories table, you'll see an oddity with the way MySQL handles transactions.

The new category you added in the transaction has a catid value that's not in sequence with the other values. It has a sequential value that is after the values temporarily assigned in the rolled-back transactions. Unfortunately, MySQL isn't able to rollback auto_increment values in transactions. Once MySQL assigns an auto_increment value to a transaction, even if you roll it back, it assigns the next auto_increment value.

Transactions are a crucial piece of your checkout process. You'll use them in your PHP code for the final checkout program. However, there's one more feature left to discuss before you can start coding. Follow me to Chapter 3, and let's work on some more database theory.

Updating the Inventory

Part of the Food Store application allows your store manager to track product inventory in real time, as customers purchase products. This means that you need to have a system that automatically subtracts purchased products from the quantity data field in the products table.

This requires using the UPDATE SQL statement, along with a little math. If this isn't something you've ever done before in the database world, don't worry—there's not much to it.

The SQL language allows you to perform mathematical operations on table data fields. The mathematical operations apply to any record in the table that meets the WHERE clause filter. So be careful when working with operations.

Here's an example of updating the quantity of a product:

UPDATE products SET quantity = quantity - 1 WHERE prodid = 1;

This statement takes the value contained in the quantity data field, subtracts one from it, and stores it back in the quantity data field of the record. It performs this task on all records that match the WHERE clause. So be careful you remember to include the WHERE clause.

This works great for tracking your inventory, but there's one small challenge you'll run into and have to solve.

Adding Table Constraints

When a customer purchases an item, you just add the UPDATE statement to the transaction so it subtracts the appropriate quantity of a product from the running inventory. Unfortunately, the way the table is set up, when the inventory gets to zero, the next update will just happily set the quantity to a negative value.

It would be nice if we could prevent customers from placing an order for a product that's out of stock. If you remember the discussion from the storefront PHP code, before allowing a customer to place a product in his or her shopping cart, you make sure there's enough of the product in stock. However, since you don't immediately remove that amount from the products table quantity data field, a situation could occur when other customers check out before the customer. So when that customer checks out the product is no longer in stock.

You can use a MySQL table constraint to control this situation. The table constraint places a restriction on the table that must be followed. You can force the UPDATE statement to fail if you attempt to subtract more of a product than the quantity in inventory. This requires a little SQL trickery.

By default, when you create the quantity data field as an integer data type, MySQL assigns it as a signed integer. A signed integer can contain both positive and negative values. If the quantity value of a product is zero, and you perform an UPDATE statement that removes one from the quantity value, the new quantity value just becomes -1. This isn't a good way to keep your inventory.

Instead, you need a method to prevent transactions from succeeding if they'd force the quantity value to be negative. This requires using two features of MySQL:

• Creating an unsigned integer data type constraint.
• Setting MySQL to strictly enforce table constraints.

For the first piece of the puzzle, you just need to change the attribute of the quantity data type from a signed integer to an unsigned integer. The unsigned constraint only allows positive integer values in the data field. This prevents the data field from containing negative values or processing an UPDATE that would result in a negative value.

You can do this either by using the graphical phpMyAdmin tool or using the MySQL Console command line interface. To change the data type attribute on an existing data field using the MySQL Console, you need to use the ALTER TABLE SQL statement.

This powerful SQL statement allows you to alter existing configuration information for a table. You redefine an existing data field within the table by specifying the CHANGE option in the ALTER TABLE statement:

ALTER TABLE products CHANGE quantity quantity int unsigned not null;

This statement changes the quantity data field to add the unsigned constraint. Notice that the quantity data field name appears twice in the statement. This looks weird, but that's how you need to do it. Let's perform this on the products table in the store database. Just follow these steps:

1. Start the WampServer and log into the MySQL Console
2. At the prompt, type:

   use store;

3. At the next prompt, type:

   ALTER TABLE products CHANGE quantity quantity int unsigned not null;

Now the quantity data field is set so it can only contain positive integer values.

Forcing Table Constraints

You might now be lulled into a false sense of security. With the quantity data field now set with the unsigned constraint, you might think that MySQL will prevent the creation of a negative value there. Wrong! Here's an example of what I mean.

Creating a 'negative' number in an unsigned data field

If you attempt to subtract more than the quantity value, something odd happens. Instead of producing an error message, MySQL performs the subtraction. The data value produced isn't what you think it should be. Instead of a negative value, a large positive value appears!

The trouble is that the integer value wraps around, creating an extremely large positive value instead of the negative value. This is a feature of unsigned values. Instead of showing a negative number, the value goes to the top of the data value range and starts working downward. This wraparound is due to the configuration of bits in the data field and how the MySQL server performs binary arithmetic.

This feature is an issue with MySQL. Originally, MySQL didn't support table constraints. To provide backward compatibility, by default MySQL permits functions that violate table constraints. You need to change that for your inventory algorithm to work.

The sql_mode setting determines how MySQL handles data validation checks. By default, MySQL doesn't perform validation checks on data in SQL statements, so invalid data can be inserted into a table (such as in the case of our unsigned example above).

To prevent this from happening, you must change the sql_mode of your MySQL session. By setting the sql_mode to STRICT_ALL_TABLES, MySQL will enforce strict data validation checks on all data entry constraints.

The SQL statement you need to use is:

set sql_mode = 'STRICT_ALL_TABLES';

When you set this, MySQL validates all data operations, including the UPDATE statement.

Enforcing data validation checks in MySQL

Now, if an UPDATE statement causes the quantity data field to produce a negative value, the UPDATE statement fails, and no operation occurs on the record. This behavior is exactly what we want in our application.

We can add the sql_mode setting in our transaction statements, forcing strict data validation checks on our UPDATE statements. If an UPDATE statement attempts to create a negative inventory, the UPDATE will fail, causing the entire transaction to fail.

Now that you've seen the theory behind our checkout process, it's time to put that theory into PHP (and SQL) code. Let's continue on to Chapter 4 and start coding!

Programming the Transactions

With all the database theory stuff out of the way, we can get back to coding. When the customer confirms the final order, they're sent to the finishorder.inc.php file. This file updates all of the database tables with the information contained in the customer's shopping cart.

As you saw earlier in this lesson, you need to modify three tables:

• A new record added to the orders table.
• A new record added to the order_items table for each product purchased.
• The quantity value updated in the products table for each product purchased.

This lets you put your newly learned transaction skills to work. You'll need to create a transaction that performs each of these database actions. If all of the database statements succeed, you can commit the transaction. If any one of the database statements fails, you must roll back the transaction and inform the customer that you can't process the order.

The finishorder.inc.php file does all this. Let's create the finishorder.inc.php file. Follow these steps:

1. Create a file called finishorder.inc.php in the store folder in the application area.
2. Open the file with a text editor and enter the following code:

Print code


<?php
   echo "<h2><u>Finalizing Order</u></h2><br>\n";
   echo "Creating order.\n";
   $custid = $_SESSION['cust'];
   $date = date("Y-m-d G:i:s");
   $status = "pending";
   $result1 = @mysql_query("Set autocommit=0");
   $result2 = @mysql_query("set sql_mode = 'STRICT_ALL_TABLES'");
   $result3 = @mysql_query("START TRANSACTION");
   $query = "INSERT INTO orders (custid, date, status) VALUES " .
          " ($custid, '$date', '$status')";
   $result4 = @mysql_query($query);
   $query = "SELECT LAST_INSERT_ID() from orders";
   $result5 = @mysql_query($query);
   $row = mysql_fetch_array($result5);
   $orderid = $row[0];
  foreach($_SESSION['cart'] as $prodid => $quantity)
{
   $query = "SELECT price FROM products where prodid = $prodid";
   $result6i = @mysql_query($query);
   $row = mysql_fetch_array($result6i);
   $price = $row[0];
   $query = "INSERT into order_items VALUES ($orderid, $prodid, $quantity, $price)";
      $result6a = @mysql_query($query);
      $query = "UPDATE products set quantity = quantity - $quantity WHERE prodid = $prodid";
      $result6b = @mysql_query($query);
      if ($result6a && $result6b)
      {
         $result6 = true;
      } else
      {
         $result6 = false;
         break;
      }
   }
   if ($result1 && $result2 && $result3 && $result4 && $result5 && $result6)
   {
      $result = @mysql_query("COMMIT");
      if ($result)
      {
         echo "Your order has been placed.<br><br>\n";
         echo "<h2>Your order number is #$orderid.<br>\n";
         echo "<h2>Please save this number for future reference.<br>Thank you!</h2>\n";
         unset($_SESSION['cart']);
      } else
      {
         echo "<h2>Sorry, we are unable to create your order at this time.</h2>\n";
         echo "<h2>Please double check product availability.</h2>\n";
      }
   } else
   {
      $result = @mysql_query("ROLLBACK");
      echo "<h2>Sorry, we are unable to create your order at this time.</h2>\n";
   }
?>

3. Save the file and exit the text editor.

That's a lot of code, but you should be able to follow along with what's happening. First, the program disables the autocommit feature and sets the sql_mode to STRICT_ALL_TABLES so the updates to the products table will work properly.

You'll notice something odd with the way I performed the queries:

$result1 = @mysql_query("Set autocommit=0");

The @ symbol in front of the mysql_query() function is an interesting feature. This suppresses any error messages that may result from the query.

You've seen before how to use the or die() feature to suppress error messages. However, the PHP preprocessor stops processing statements and automatically exits when you use this technique. You don't want that to happen in this case, as you have a transaction that you started. If a query fails, you just want to flag that it failed so you know to use the ROLLBACK statement at the end of the transaction.

You do that by assigning unique PHP variable names to each query. When you've completed all of the queries in the transaction, all we need to do is check if any query failed by logically ANDing the results. The && symbol performs the logical AND function. All of the values in the operation must be TRUE for the result to be TRUE. If a query failed, its result will be FALSE, causing the result of the AND statement to be FALSE. This is what enables you to detect if a query fails.

After preparing the transaction, you can start inserting and updating records. First, the code inserts a new record in the orders table. Next, you need to use our friend the LAST_INSERT_ID() function to retrieve just what orderid the server assigned to the new order so you can use it for the records in the order_items table.

The code uses the foreach statement to iterate through the products in the customer's shopping cart, adding each product to the order_items table. Each product relates to the order using the orderid retrieved.

Finally, the code uses the UPDATE statement for each product to update the inventory total for each product with the amount purchased by the customer. If any of the inserts or updates fails, the result variable is set to false, and the code uses the break statement to jump out of the foreach loop to stop processing products.

At the end of the process, if all of the SQL statements succeed, the code sends the COMMIT statement to the server, committing the data to the database, and displays a message with the orderid value for the customer to record.

The final checkout customer receipt

Also, if the checkout process is complete, the code removes the entire shopping cart session cookie to prevent duplicate checkout orders.

That completes the checkout process for the customer. We've covered quite a bit of ground in the last two lessons just trying to present a pleasant checkout experience for our customers. Let's go on to Chapter 5 and wrap up this lesson.

Summary

Today we covered the topic of transactions. A transaction allows us to group multiple SQL statements together to process as a single unit. If any of the statements in the transaction fails, the entire transaction fails.

MySQL allows you to create transactions so you can block database events together. You must disable the autocommit feature to enable transactions. The START TRANSACTION statement begins the block of statements contained within the transaction. To undo any statements performed in the transaction, use the ROLLBACK statement. To process the statements performed in the transaction, use the COMMIT statement.

You also learned how to use table constraints to help validate data used in the application. You used the unsigned integer data constraint to force the quantity data field in the products table to be a positive value. By setting the sql_mode value to STRICT_ALL_TABLES, you can force an UPDATE statement to fail if it sets a data field to an improper value.

This completes the basic features of the Food Store application. In the next lesson, we'll discuss a few advanced topics you can incorporate into your application if you choose. You'll see how to use MySQL regular expressions to perform complex string searches in your database data. This allows customers to search for any type of data in your database. We'll also see how the store manager can produce customized reports using the data in the application database.